CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_TEST" as

  /*------------------------------- 向mdl_stock_top_detail表中导入数据 -------------------------------------*/
  /*procedure IMPORT_MDL_STOCK_TOP_DETAIL(p_date_number in number, p_period in number) is
    -- mdl_top_stock表类型的变量
    v_mdl_top_stock MDL_TOP_STOCK%rowtype;
    \*-- 定义类型
    type_top_stock T_TOP_STOCK;
    -- 定义数组
    type_top_stock_array T_TOP_STOCK_ARRAY := T_TOP_STOCK_ARRAY();*\
    -- 列period*
    clob_period Clob;
    -- 列period*(varchar2类型)
    v_origin_period varchar2(32767);
    -- 是否继续循环。1表示是，0表示否
    v_first_do_while number := 1;
    -- 开始位置
    v_first_start_position number := 0;
    -- 结束位置
    v_first_end_position number;
    -- 每只股票的股票代码、股票名称、板块名称、涨跌百分比等信息
    v_stock_info varchar2(25600);
    -- 是否继续循环。1表示是，0表示否
    v_second_do_while number := 1;
    -- 开始位置
    v_second_start_position number := 0;
    -- 结束位置
    v_second_end_position number;
    -- 每一列
    v_column varchar2(25600);
    -- 股票代码、股票名称、板块名称、涨跌百分比
    v_code varchar2(128);
    v_name varchar2(128);
    v_board_name varchar2(128);
    v_up_down_percentage number;
    -- 查找最近p_date_number天的记录
    cursor cur_latest is
      select *
        from (select *
                from mdl_top_stock t
               order by t.date_ desc)
       where rownum <= p_date_number;
  begin
    open cur_latest;
    loop
      fetch cur_latest
        into v_mdl_top_stock;
      exit when cur_latest%notfound;
  
      -- type_top_stock_array := T_TOP_STOCK_ARRAY();
  
      dbms_output.put_line('date_：【' || v_mdl_top_stock.date_ || '】');
      
      -- 根据参数p_period确定导入一列的数据
      if p_period = 21 then
         clob_period := v_mdl_top_stock.period21;
      end if;
      if p_period = 34 then
         clob_period := v_mdl_top_stock.period34;
      end if;
      if p_period = 55 then
         clob_period := v_mdl_top_stock.period55;
      end if;
      -- 将clob类型转换为varchar2类型
      v_origin_period := to_char(substr(clob_period, 1, dbms_lob.getlength(clob_period)));
      -- 重置参数
      v_first_do_while := 1;
      v_first_start_position := 0;
  
      -- 提取每只股票的股票代码、股票名称、板块名称、涨跌百分比等信息
      while v_first_do_while = 1 loop
        v_first_end_position := instr(v_origin_period, CHR(10), v_first_start_position + 1, 1);
        if v_first_end_position <> 0 then
           v_stock_info := substr(v_origin_period, v_first_start_position + 1, v_first_end_position - v_first_start_position);
           v_first_start_position := v_first_end_position;
  
           -- 重置参数
            v_second_do_while := 1;
            v_second_start_position := 0;
  
            -- 提取每只股票的排名、涨跌百分比等信息
            while v_second_do_while <> 0 loop
              v_second_end_position := instr(v_stock_info, '&', v_second_start_position + 1, 1);
              if v_second_end_position <> 0 then
                 v_column := substr(v_stock_info, v_second_start_position + 1, v_second_end_position - v_second_start_position - 1);
                 v_second_start_position := v_second_end_position;
                 -- 股票代码
                 if v_second_do_while = 2 then
                    v_code := v_column;
                 end if;
                 -- 股票名称
                 if v_second_do_while = 3 then
                    v_name := v_column;
                 end if;
                 -- 板块名称
                 if v_second_do_while = 4 then
                    v_board_name := v_column;
                 end if;
                 -- 涨跌百分比
                 v_second_do_while := v_second_do_while + 1;
                 if v_second_do_while = 5 then
                    v_up_down_percentage := to_number(replace(substr(v_stock_info, v_second_end_position + 1), chr(10), ''));
                 end if;
              else
                 v_second_do_while := 0;
              end if;
            end loop;
            insert into mdl_top_stock_detail(date_, code_, name_, board_name, up_down_percentage, period_type) 
            values(v_mdl_top_stock.date_, v_code, v_name, v_board_name, v_up_down_percentage, p_period);
            commit;
        else
           v_first_do_while := 0;
        end if;
      end loop;
  
    end loop;
    close cur_latest;
  end IMPORT_MDL_STOCK_TOP_DETAIL;*/

  /*-- 计算MACD金叉、close_price金叉MA5、hei_kin_ashi上涨趋势、KD金叉算法在周线级别KD金叉时的成功率 --*/
  /*procedure CAL_SUCCESS_RATE_WITH_W_KD_G_C is
    -- 表mdl_macd_gold_cross类型的行对象
    v_macd_gold_cross mdl_macd_gold_cross%rowtype;
    -- 表mdl_close_price_ma5_gold_cross类型的行对象
    v_close_price_ma5_gold_cross mdl_close_price_ma5_gold_cross%rowtype;
    -- 表mdl_hei_kin_ashi_up_down类型的行对象
    v_hei_kin_ashi_up_down mdl_hei_kin_ashi_up_down%rowtype;
    -- 表mdl_kd_gold_cross类型的行对象
    v_kd_gold_cross mdl_kd_gold_cross%rowtype;
    -- 记录是否存在
    v_exist number;
    -- 交易成功的记录的数量
    v_success_num number := 0;
    -- 交易记录的数量
    v_num number := 0;
    -- 表mdl_macd_gold_cross中的记录
    cursor cur_macd_gold_cross is
      select * from mdl_macd_gold_cross t;
    -- 表mdl_close_price_ma5_gold_cross中的记录
    cursor cur_close_price_ma5_gold_cross is
      select * from mdl_close_price_ma5_gold_cross t;
    -- 表mdl_hei_kin_ashi_up_down中的记录
    cursor cur_hei_kin_ashi_up_down is
      select * from mdl_hei_kin_ashi_up_down t;
    -- 表mdl_kd_gold_cross中的记录
    cursor cur_kd_gold_cross is
      select * from mdl_kd_gold_cross t;
  begin
    ----------------------------------- MACD金叉 ---------------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_macd_gold_cross;
    loop
      fetch cur_macd_gold_cross
        into v_macd_gold_cross;
      exit when cur_macd_gold_cross%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_macd_gold_cross.stock_code
                 and t.begin_date < v_macd_gold_cross.buy_date
                 and t.end_date < v_macd_gold_cross.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_macd_gold_cross.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_macd_gold_cross;
  
    -- 打印成功率
    pkg_tool.DEBUG('MACD金叉，并且周线级别KD金叉的成功率是：' ||
                   v_success_num / v_num * 100);
  
    ------------------------------- close_price金叉MA5 -----------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_close_price_ma5_gold_cross;
    loop
      fetch cur_close_price_ma5_gold_cross
        into v_close_price_ma5_gold_cross;
      exit when cur_close_price_ma5_gold_cross%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_close_price_ma5_gold_cross.stock_code
                 and t.begin_date < v_close_price_ma5_gold_cross.buy_date
                 and t.end_date < v_close_price_ma5_gold_cross.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_close_price_ma5_gold_cross.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_close_price_ma5_gold_cross;
  
    -- 打印成功率
    pkg_tool.DEBUG('close_price金叉MA5，并且周线级别KD金叉的成功率是：' ||
                   v_success_num / v_num * 100);
  
    ------------------------------- hei_kin_ashi上涨趋势 --------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_hei_kin_ashi_up_down;
    loop
      fetch cur_hei_kin_ashi_up_down
        into v_hei_kin_ashi_up_down;
      exit when cur_hei_kin_ashi_up_down%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_hei_kin_ashi_up_down.stock_code
                 and t.begin_date < v_hei_kin_ashi_up_down.buy_date
                 and t.end_date < v_hei_kin_ashi_up_down.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_hei_kin_ashi_up_down.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_hei_kin_ashi_up_down;
  
    -- 打印成功率
    pkg_tool.DEBUG('hei_kin_ashi上涨趋势，并且周线级别KD金叉的成功率是：' ||
                   v_success_num / v_num * 100);
  
    ----------------------------------- KD金叉 ---------------------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_kd_gold_cross;
    loop
      fetch cur_kd_gold_cross
        into v_kd_gold_cross;
      exit when cur_kd_gold_cross%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_kd_gold_cross.stock_code
                 and t.begin_date < v_kd_gold_cross.buy_date
                 and t.end_date < v_kd_gold_cross.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_kd_gold_cross.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_kd_gold_cross;
  
    -- 打印成功率
    pkg_tool.DEBUG('KD金叉，并且周线级别KD金叉的成功率是：' || v_success_num / v_num * 100);
  end CAL_SUCCESS_RATE_WITH_W_KD_G_C;*/

  /*--------------------- 根据ma的形态和金叉死叉的方式来计算交易数据 -----------------------*/
  procedure CAL_MA_PATTERN_OVERSOLD(p_ma_pattern                 varchar2,
                                    p_gold_cross_dead_cross_type varchar2,
                                    p_direction                  number) is
    -- 股票code
    v_code varchar(50);
    -- 日期
    v_date date;
    -- 为0表示还没有买，为1表示已经买了
    v_is_buy number := 0;
    -- 为0表示还没有卖，为1表示已经卖了
    v_is_sell number := 0;
    -- 买入日期
    v_buy_date date;
    -- 买入价格
    v_buy_price number;
    -- 收盘价
    v_close_price number;
    -- ma5
    v_ma5 number;
    -- 卖出日期
    v_sell_date date;
    -- 卖出价格
    v_sell_price number;
    -- 收益率
    v_profit_loss number;
    -- 累计收益
    v_accumulative_profit_loss number;
    -- robot_stock_filter表类型的变量
    row_mdl_stock_analysis        MDL_STOCK_ANALYSIS%rowtype;
    row_mdl_single_stock_analysis MDL_STOCK_ANALYSIS%rowtype;
    -- 所有股票的code
    cursor cur_stock_code is
      select distinct t.code_ from stock_transaction_data_all t;
    -- 某一只股票空头排列的所有记录
    cursor cur_oversold is
      select *
        from MDL_STOCK_ANALYSIS t
       where t.close_price_ma_order = p_ma_pattern
         and t.code_ = v_code
       order by t.code_, t.date_ asc;
    -- 某一只股票在某个日期之后的所有交易记录
    cursor cur_single_mdl_stock_analysis is
      select *
        from mdl_stock_analysis t
       where t.code_ = v_code
         and t.date_ >= v_date
       order by t.date_ asc;
  begin
    open cur_stock_code;
    loop
      fetch cur_stock_code
        into v_code;
      exit when cur_stock_code%notfound;
      -- 初始化
      v_date                     := null;
      v_is_buy                   := 0;
      v_is_sell                  := 0;
      v_buy_date                 := null;
      v_buy_price                := null;
      v_close_price              := null;
      v_ma5                      := null;
      v_sell_date                := null;
      v_sell_price               := null;
      v_profit_loss              := null;
      v_accumulative_profit_loss := 100;
    
      open cur_oversold;
      loop
        fetch cur_oversold
          into row_mdl_stock_analysis;
        exit when cur_oversold%notfound;
        -- 跳过已经交易的记录
        if (row_mdl_stock_analysis.date_ <= v_sell_date and p_direction = 1) or
           (row_mdl_stock_analysis.date_ <= v_buy_date and p_direction = -1) then
          continue;
        end if;
        v_date := row_mdl_stock_analysis.date_;
      
        open cur_single_mdl_stock_analysis;
        loop
          fetch cur_single_mdl_stock_analysis
            into row_mdl_single_stock_analysis;
          exit when cur_single_mdl_stock_analysis%notfound;
        
          -- 做多
          if p_direction = 1 then
            -- 如果没有买入，并且前一天还是空头排列
            if v_is_buy = 0 and row_mdl_single_stock_analysis.close_price_ma_order !=
               p_ma_pattern then
              begin
                select t.close_price
                  into v_buy_price
                  from stock_transaction_data_all t
                 where t.code_ = row_mdl_single_stock_analysis.code_
                   and t.date_ = row_mdl_single_stock_analysis.date_;
              exception
                when no_data_found then
                  continue;
              end;
              v_buy_date := row_mdl_single_stock_analysis.date_;
              v_is_buy   := 1;
            end if;
          
            -- 如果已经买入，并且当天的close_price小于等于五日均线
            if v_is_buy = 1 then
              begin
                select t.close_price, t.ma5
                  into v_close_price, v_ma5
                  from stock_transaction_data_all t
                 where t.code_ = row_mdl_single_stock_analysis.code_
                   and t.date_ = row_mdl_single_stock_analysis.date_;
              exception
                when no_data_found then
                  continue;
              end;
            
              if v_close_price <= v_ma5 then
              
                v_sell_date  := row_mdl_single_stock_analysis.date_;
                v_sell_price := v_close_price;
              
                -- 判断这只股票在指定时间范围内是否出现了除权
                if fnc_stock_xr(row_mdl_single_stock_analysis.code_,
                                to_char(v_buy_date, 'yyyy-mm-dd'),
                                to_char(v_sell_date, 'yyyy-mm-dd')) = 1 then
                  exit;
                end if;
              
                -- 计算收益率和累计收益
                v_profit_loss              := (v_sell_price - v_buy_price) /
                                              v_buy_price * 100;
                v_accumulative_profit_loss := v_accumulative_profit_loss *
                                              (1 + v_profit_loss / 100);
                -- 向表mdl_ma_pattern_oversold中插入数据
                insert into mdl_ma_pattern_oversold
                  (STOCK_CODE,
                   BUY_DATE,
                   BUY_PRICE,
                   Sell_Date,
                   Sell_Price,
                   PROFIT_LOSS,
                   ACCUMULATIVE_PROFIT_LOSS,
                   BUY_SELL_PATTERN,
                   MODEL_ID,
                   DIRECTION)
                values
                  (row_mdl_single_stock_analysis.code_,
                   v_buy_date,
                   v_buy_price,
                   v_sell_date,
                   v_sell_price,
                   v_profit_loss,
                   v_accumulative_profit_loss,
                   p_gold_cross_dead_cross_type || '_' || p_ma_pattern,
                   1,
                   1);
                commit;
              
                -- 重置
                v_is_buy := 0;
              
                exit;
              end if;
            end if;
          end if;
        
          -- 做空
          if p_direction = -1 then
            -- 如果没有卖出，并且前一天还是多头排列
            if v_is_sell = 0 and row_mdl_single_stock_analysis.close_price_ma_order !=
               p_ma_pattern then
              begin
                select t.close_price
                  into v_sell_price
                  from stock_transaction_data_all t
                 where t.code_ = row_mdl_single_stock_analysis.code_
                   and t.date_ = row_mdl_single_stock_analysis.date_;
              exception
                when no_data_found then
                  continue;
              end;
              v_sell_date := row_mdl_single_stock_analysis.date_;
              v_is_sell   := 1;
            end if;
          
            -- 如果已经卖出，并且当天的close_price大于等于五日均线
            if v_is_sell = 1 then
              begin
                select t.close_price, t.ma5
                  into v_close_price, v_ma5
                  from stock_transaction_data_all t
                 where t.code_ = row_mdl_single_stock_analysis.code_
                   and t.date_ = row_mdl_single_stock_analysis.date_;
              exception
                when no_data_found then
                  continue;
              end;
            
              if v_close_price >= v_ma5 then
              
                v_buy_date  := row_mdl_single_stock_analysis.date_;
                v_buy_price := v_close_price;
              
                -- 判断这只股票在指定时间范围内是否出现了除权
                if fnc_stock_xr(row_mdl_single_stock_analysis.code_,
                                to_char(v_sell_date, 'yyyy-mm-dd'),
                                to_char(v_buy_date, 'yyyy-mm-dd')) = 1 then
                  exit;
                end if;
              
                -- 计算收益率和累计收益
                v_profit_loss              := (v_buy_price - v_sell_price) /
                                              v_sell_price * 100;
                v_accumulative_profit_loss := v_accumulative_profit_loss *
                                              (1 + v_profit_loss / 100);
                -- 向表mdl_ma_pattern_oversold中插入数据
                insert into mdl_ma_pattern_oversold
                  (STOCK_CODE,
                   BUY_DATE,
                   BUY_PRICE,
                   Sell_Date,
                   Sell_Price,
                   PROFIT_LOSS,
                   ACCUMULATIVE_PROFIT_LOSS,
                   BUY_SELL_PATTERN,
                   MODEL_ID,
                   DIRECTION)
                values
                  (row_mdl_single_stock_analysis.code_,
                   v_buy_date,
                   v_buy_price,
                   v_sell_date,
                   v_sell_price,
                   v_profit_loss,
                   v_accumulative_profit_loss,
                   p_gold_cross_dead_cross_type || '_' || p_ma_pattern,
                   1,
                   -1);
                commit;
              
                -- 重置
                v_is_sell := 0;
              
                exit;
              end if;
            end if;
          end if;
        
        end loop;
        close cur_single_mdl_stock_analysis;
      
      end loop;
      close cur_oversold;
    
    end loop;
    close cur_stock_code;
  end CAL_MA_PATTERN_OVERSOLD;

  /*--------------- 选择某段时间内，4中算法中（金叉/死叉），成功率最高的股票 ---------------*/
  procedure filter_by_max_success_rate(p_need_stock_number          number,
                                       p_macd_begin_date            varchar,
                                       p_macd_end_date              varchar,
                                       p_close_price_ma5_begin_date varchar,
                                       p_close_price_ma5_end_date   varchar,
                                       p_hei_kin_ashi_begin_date    varchar,
                                       p_hei_kin_ashi_end_date      varchar,
                                       p_kd_begin_date              varchar,
                                       p_kd_end_date                varchar) is
    -- 平均交易次数
    v_macd_gold_cross_avg_number   number;
    v_c_p_ma5_g_c_avg_number       number;
    v_hei_kin_ashi_up_avg_number   number;
    v_hei_kin_ashi_down_avg_number number;
    v_kd_gold_cross_avg_number     number;
    -- macd金叉成功率最高的股票
    cursor cur_macd_gold_cross is
      select t.stock_code,
             (select count(*)
                from mdl_macd_gold_cross t1
               where t1.stock_code = t.stock_code
                 and t1.buy_date between
                     to_date(p_macd_begin_date, 'yyyy-mm-dd') and
                     to_date(p_macd_end_date, 'yyyy-mm-dd')
                 and t1.profit_loss > 0) / count(t.id) success_rate,
             count(*) c_
        from mdl_macd_gold_cross t
       where t.buy_date between to_date(p_macd_begin_date, 'yyyy-mm-dd') and
             to_date(p_macd_end_date, 'yyyy-mm-dd')
       group by t.stock_code
      having count(*) > (select avg(tt.c_) / 3
                           from (select t.stock_code,
                                        (select count(*)
                                           from mdl_macd_gold_cross t1
                                          where t1.stock_code = t.stock_code
                                            and t1.buy_date between
                                                to_date(p_macd_begin_date,
                                                        'yyyy-mm-dd') and
                                                to_date(p_macd_end_date,
                                                        'yyyy-mm-dd')
                                            and t1.profit_loss > 0) /
                                        count(t.id) success_rate,
                                        count(*) c_
                                   from mdl_macd_gold_cross t
                                  where t.buy_date between
                                        to_date(p_macd_begin_date,
                                                'yyyy-mm-dd') and
                                        to_date(p_macd_end_date, 'yyyy-mm-dd')
                                  group by t.stock_code
                                  order by success_rate desc) tt)
       order by success_rate desc;
    -- close_price金叉ma5成功率最高的股票
    cursor cur_close_price_ma5_gold_cross is
      select t.stock_code,
             (select count(*)
                from mdl_close_price_ma5_gold_cross t1
               where t1.stock_code = t.stock_code
                 and t1.buy_date between
                     to_date(p_close_price_ma5_begin_date, 'yyyy-mm-dd') and
                     to_date(p_close_price_ma5_end_date, 'yyyy-mm-dd')
                 and t1.profit_loss > 0) / count(t.id) success_rate,
             count(*) c_
        from mdl_close_price_ma5_gold_cross t
       where t.buy_date between
             to_date(p_close_price_ma5_begin_date, 'yyyy-mm-dd') and
             to_date(p_close_price_ma5_end_date, 'yyyy-mm-dd')
       group by t.stock_code
      having count(*) > (select avg(tt.c_) / 3
                           from (select t.stock_code,
                                        (select count(*)
                                           from mdl_close_price_ma5_gold_cross t1
                                          where t1.stock_code = t.stock_code
                                            and t1.buy_date between
                                                to_date(p_close_price_ma5_begin_date,
                                                        'yyyy-mm-dd') and
                                                to_date(p_close_price_ma5_end_date,
                                                        'yyyy-mm-dd')
                                            and t1.profit_loss > 0) /
                                        count(t.id) success_rate,
                                        count(*) c_
                                   from mdl_close_price_ma5_gold_cross t
                                  where t.buy_date between
                                        to_date(p_close_price_ma5_begin_date,
                                                'yyyy-mm-dd') and
                                        to_date(p_close_price_ma5_end_date,
                                                'yyyy-mm-dd')
                                  group by t.stock_code
                                  order by success_rate desc) tt)
       order by success_rate desc;
    -- hei_kin_ashi上升趋势成功率最高的股票
    cursor cur_hei_kin_ashi_up_down is
      select t.stock_code,
             (select count(*)
                from mdl_hei_kin_ashi_up_down t1
               where t1.stock_code = t.stock_code
                 and t1.buy_date between
                     to_date(p_hei_kin_ashi_begin_date, 'yyyy-mm-dd') and
                     to_date(p_hei_kin_ashi_end_date, 'yyyy-mm-dd')
                 and t1.profit_loss > 0) / count(t.id) success_rate,
             count(*) c_
        from mdl_hei_kin_ashi_up_down t
       where t.buy_date between
             to_date(p_hei_kin_ashi_begin_date, 'yyyy-mm-dd') and
             to_date(p_hei_kin_ashi_end_date, 'yyyy-mm-dd')
       group by t.stock_code
      having count(*) > (select avg(tt.c_) / 3
                           from (select t.stock_code,
                                        (select count(*)
                                           from mdl_hei_kin_ashi_up_down t1
                                          where t1.stock_code = t.stock_code
                                            and t1.buy_date between
                                                to_date(p_hei_kin_ashi_begin_date,
                                                        'yyyy-mm-dd') and
                                                to_date(p_hei_kin_ashi_end_date,
                                                        'yyyy-mm-dd')
                                            and t1.profit_loss > 0) /
                                        count(t.id) success_rate,
                                        count(*) c_
                                   from mdl_hei_kin_ashi_up_down t
                                  where t.buy_date between
                                        to_date(p_hei_kin_ashi_begin_date,
                                                'yyyy-mm-dd') and
                                        to_date(p_hei_kin_ashi_end_date,
                                                'yyyy-mm-dd')
                                  group by t.stock_code
                                  order by success_rate desc) tt)
       order by success_rate desc;
    -- kd金叉成功率最高的股票
    cursor cur_kd_gold_cross is
      select t.stock_code,
             (select count(*)
                from mdl_kd_gold_cross t1
               where t1.stock_code = t.stock_code
                 and t1.buy_date between
                     to_date(p_kd_begin_date, 'yyyy-mm-dd') and
                     to_date(p_kd_end_date, 'yyyy-mm-dd')
                 and t1.profit_loss > 0) / count(t.id) success_rate,
             count(*) c_
        from mdl_kd_gold_cross t
       where t.buy_date between to_date(p_kd_begin_date, 'yyyy-mm-dd') and
             to_date(p_kd_end_date, 'yyyy-mm-dd')
       group by t.stock_code
      having count(*) > (select avg(tt.c_) / 3
                           from (select t.stock_code,
                                        (select count(*)
                                           from mdl_kd_gold_cross t1
                                          where t1.stock_code = t.stock_code
                                            and t1.buy_date between
                                                to_date(p_kd_begin_date,
                                                        'yyyy-mm-dd') and
                                                to_date(p_kd_end_date,
                                                        'yyyy-mm-dd')
                                            and t1.profit_loss > 0) /
                                        count(t.id) success_rate,
                                        count(*) c_
                                   from mdl_kd_gold_cross t
                                  where t.buy_date between
                                        to_date(p_kd_begin_date, 'yyyy-mm-dd') and
                                        to_date(p_kd_end_date, 'yyyy-mm-dd')
                                  group by t.stock_code
                                  order by success_rate desc) tt)
       order by success_rate desc;
  begin
    delete from robot_stock_filter t_
     where t_.stock_code not in
           (select ttt.stock_code
              from (select *
                      from (select t.stock_code,
                                   (select count(*)
                                      from mdl_macd_gold_cross t1
                                     where t1.stock_code = t.stock_code
                                       and t1.buy_date between
                                           to_date(p_macd_begin_date,
                                                   'yyyy-mm-dd') and
                                           to_date(p_macd_end_date,
                                                   'yyyy-mm-dd')
                                       and t1.profit_loss > 0) / count(t.id) success_rate,
                                   count(*) c_,
                                   1 filter_type
                              from mdl_macd_gold_cross t
                             where t.buy_date between
                                   to_date(p_macd_begin_date, 'yyyy-mm-dd') and
                                   to_date(p_macd_end_date, 'yyyy-mm-dd')
                             group by t.stock_code
                            having count(*) > (select avg(tt.c_) / 3
                                                from (select t.stock_code,
                                                             (select count(*)
                                                                from mdl_macd_gold_cross t1
                                                               where t1.stock_code =
                                                                     t.stock_code
                                                                 and t1.buy_date between
                                                                     to_date(p_macd_begin_date,
                                                                             'yyyy-mm-dd') and
                                                                     to_date(p_macd_end_date,
                                                                             'yyyy-mm-dd')
                                                                 and t1.profit_loss > 0) /
                                                             count(t.id) success_rate,
                                                             count(*) c_
                                                        from mdl_macd_gold_cross t
                                                       where t.buy_date between
                                                             to_date(p_macd_begin_date,
                                                                     'yyyy-mm-dd') and
                                                             to_date(p_macd_end_date,
                                                                     'yyyy-mm-dd')
                                                       group by t.stock_code
                                                       order by success_rate desc) tt)
                            --order by success_rate desc
                            union
                            select t.stock_code,
                                   (select count(*)
                                      from mdl_close_price_ma5_gold_cross t1
                                     where t1.stock_code = t.stock_code
                                       and t1.buy_date between
                                           to_date(p_close_price_ma5_begin_date,
                                                   'yyyy-mm-dd') and
                                           to_date(p_close_price_ma5_end_date,
                                                   'yyyy-mm-dd')
                                       and t1.profit_loss > 0) / count(t.id) success_rate,
                                   count(*) c_,
                                   3 filter_type
                              from mdl_close_price_ma5_gold_cross t
                             where t.buy_date between
                                   to_date(p_close_price_ma5_begin_date,
                                           'yyyy-mm-dd') and
                                   to_date(p_close_price_ma5_end_date,
                                           'yyyy-mm-dd')
                             group by t.stock_code
                            having count(*) > (select avg(tt.c_) / 3
                                                from (select t.stock_code,
                                                             (select count(*)
                                                                from mdl_close_price_ma5_gold_cross t1
                                                               where t1.stock_code =
                                                                     t.stock_code
                                                                 and t1.buy_date between
                                                                     to_date(p_close_price_ma5_begin_date,
                                                                             'yyyy-mm-dd') and
                                                                     to_date(p_close_price_ma5_end_date,
                                                                             'yyyy-mm-dd')
                                                                 and t1.profit_loss > 0) /
                                                             count(t.id) success_rate,
                                                             count(*) c_
                                                        from mdl_close_price_ma5_gold_cross t
                                                       where t.buy_date between
                                                             to_date(p_close_price_ma5_begin_date,
                                                                     'yyyy-mm-dd') and
                                                             to_date(p_close_price_ma5_end_date,
                                                                     'yyyy-mm-dd')
                                                       group by t.stock_code
                                                       order by success_rate desc) tt)
                            --order by success_rate desc
                            union
                            select t.stock_code,
                                   (select count(*)
                                      from mdl_hei_kin_ashi_up_down t1
                                     where t1.stock_code = t.stock_code
                                       and t1.buy_date between
                                           to_date(p_hei_kin_ashi_begin_date,
                                                   'yyyy-mm-dd') and
                                           to_date(p_hei_kin_ashi_end_date,
                                                   'yyyy-mm-dd')
                                       and t1.profit_loss > 0) / count(t.id) success_rate,
                                   count(*) c_,
                                   4 filter_type
                              from mdl_hei_kin_ashi_up_down t
                             where t.buy_date between
                                   to_date(p_hei_kin_ashi_begin_date,
                                           'yyyy-mm-dd') and
                                   to_date(p_hei_kin_ashi_end_date,
                                           'yyyy-mm-dd')
                             group by t.stock_code
                            having count(*) > (select avg(tt.c_) / 3
                                                from (select t.stock_code,
                                                             (select count(*)
                                                                from mdl_hei_kin_ashi_up_down t1
                                                               where t1.stock_code =
                                                                     t.stock_code
                                                                 and t1.buy_date between
                                                                     to_date(p_hei_kin_ashi_begin_date,
                                                                             'yyyy-mm-dd') and
                                                                     to_date(p_hei_kin_ashi_end_date,
                                                                             'yyyy-mm-dd')
                                                                 and t1.profit_loss > 0) /
                                                             count(t.id) success_rate,
                                                             count(*) c_
                                                        from mdl_hei_kin_ashi_up_down t
                                                       where t.buy_date between
                                                             to_date(p_hei_kin_ashi_begin_date,
                                                                     'yyyy-mm-dd') and
                                                             to_date(p_hei_kin_ashi_end_date,
                                                                     'yyyy-mm-dd')
                                                       group by t.stock_code
                                                       order by success_rate desc) tt)
                            --order by success_rate desc
                            union
                            select t.stock_code,
                                   (select count(*)
                                      from mdl_kd_gold_cross t1
                                     where t1.stock_code = t.stock_code
                                       and t1.buy_date between
                                           to_date(p_kd_begin_date,
                                                   'yyyy-mm-dd') and
                                           to_date(p_kd_end_date, 'yyyy-mm-dd')
                                       and t1.profit_loss > 0) / count(t.id) success_rate,
                                   count(*) c_,
                                   7 filter_type
                              from mdl_kd_gold_cross t
                             where t.buy_date between
                                   to_date(p_kd_begin_date, 'yyyy-mm-dd') and
                                   to_date(p_kd_end_date, 'yyyy-mm-dd')
                             group by t.stock_code
                            having count(*) > (select avg(tt.c_) / 3
                                                from (select t.stock_code,
                                                             (select count(*)
                                                                from mdl_kd_gold_cross t1
                                                               where t1.stock_code =
                                                                     t.stock_code
                                                                 and t1.buy_date between
                                                                     to_date(p_kd_begin_date,
                                                                             'yyyy-mm-dd') and
                                                                     to_date(p_kd_end_date,
                                                                             'yyyy-mm-dd')
                                                                 and t1.profit_loss > 0) /
                                                             count(t.id) success_rate,
                                                             count(*) c_
                                                        from mdl_kd_gold_cross t
                                                       where t.buy_date between
                                                             to_date(p_kd_begin_date,
                                                                     'yyyy-mm-dd') and
                                                             to_date(p_kd_end_date,
                                                                     'yyyy-mm-dd')
                                                       group by t.stock_code
                                                       order by success_rate desc) tt)
                             order by success_rate desc) total
                     order by total.success_rate desc) ttt
             where rownum <= p_need_stock_number);
    commit;
  end filter_by_max_success_rate;

  /*-- 计算MACD金叉、close_price金叉MA5、hei_kin_ashi上涨趋势、KD金叉算法在周线级别KD金叉时的成功率 --*/
  /*procedure CAL_SUCCESS_RATE_WITH_W_KD_G_C is
    -- 表mdl_macd_gold_cross类型的行对象
    v_macd_gold_cross mdl_macd_gold_cross%rowtype;
    -- 表mdl_close_price_ma5_gold_cross类型的行对象
    v_close_price_ma5_gold_cross mdl_close_price_ma5_gold_cross%rowtype;
    -- 表mdl_hei_kin_ashi_up_down类型的行对象
    v_hei_kin_ashi_up_down mdl_hei_kin_ashi_up_down%rowtype;
    -- 表mdl_kd_gold_cross类型的行对象
    v_kd_gold_cross mdl_kd_gold_cross%rowtype;
    -- 记录是否存在
    v_exist number;
    -- 交易成功的记录的数量
    v_success_num number := 0;
    -- 交易记录的数量
    v_num number := 0;
    -- 表mdl_macd_gold_cross中的记录
    cursor cur_macd_gold_cross is
      select * from mdl_macd_gold_cross t;
    -- 表mdl_close_price_ma5_gold_cross中的记录
    cursor cur_close_price_ma5_gold_cross is
      select * from mdl_close_price_ma5_gold_cross t;
    -- 表mdl_hei_kin_ashi_up_down中的记录
    cursor cur_hei_kin_ashi_up_down is
      select * from mdl_hei_kin_ashi_up_down t;
    -- 表mdl_kd_gold_cross中的记录
    cursor cur_kd_gold_cross is
      select * from mdl_kd_gold_cross t;
  begin
    ----------------------------------- MACD金叉 ---------------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_macd_gold_cross;
    loop
      fetch cur_macd_gold_cross
        into v_macd_gold_cross;
      exit when cur_macd_gold_cross%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_macd_gold_cross.stock_code
                 and t.begin_date < v_macd_gold_cross.buy_date
                 and t.end_date < v_macd_gold_cross.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_macd_gold_cross.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_macd_gold_cross;
  
    -- 打印成功率
    pkg_tool.DEBUG('MACD金叉，并且周线级别KD金叉的成功率是：' ||
                   v_success_num / v_num * 100);
  
    ------------------------------- close_price金叉MA5 -----------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_close_price_ma5_gold_cross;
    loop
      fetch cur_close_price_ma5_gold_cross
        into v_close_price_ma5_gold_cross;
      exit when cur_close_price_ma5_gold_cross%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_close_price_ma5_gold_cross.stock_code
                 and t.begin_date < v_close_price_ma5_gold_cross.buy_date
                 and t.end_date < v_close_price_ma5_gold_cross.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_close_price_ma5_gold_cross.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_close_price_ma5_gold_cross;
  
    -- 打印成功率
    pkg_tool.DEBUG('close_price金叉MA5，并且周线级别KD金叉的成功率是：' ||
                   v_success_num / v_num * 100);
  
    ------------------------------- hei_kin_ashi上涨趋势 --------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_hei_kin_ashi_up_down;
    loop
      fetch cur_hei_kin_ashi_up_down
        into v_hei_kin_ashi_up_down;
      exit when cur_hei_kin_ashi_up_down%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_hei_kin_ashi_up_down.stock_code
                 and t.begin_date < v_hei_kin_ashi_up_down.buy_date
                 and t.end_date < v_hei_kin_ashi_up_down.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_hei_kin_ashi_up_down.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_hei_kin_ashi_up_down;
  
    -- 打印成功率
    pkg_tool.DEBUG('hei_kin_ashi上涨趋势，并且周线级别KD金叉的成功率是：' ||
                   v_success_num / v_num * 100);
  
    ----------------------------------- KD金叉 ---------------------------------------
    v_success_num := 0;
    v_num         := 0;
    open cur_kd_gold_cross;
    loop
      fetch cur_kd_gold_cross
        into v_kd_gold_cross;
      exit when cur_kd_gold_cross%notfound;
    
      -- 判断每条记录时，都初始化为0
      v_exist := 0;
    
      -- 判断前一周的KD是否金叉
      select count(*)
        into v_exist
        from (select *
                from stock_week t
               where t.code_ = v_kd_gold_cross.stock_code
                 and t.begin_date < v_kd_gold_cross.buy_date
                 and t.end_date < v_kd_gold_cross.buy_date
               order by t.begin_date desc) sw
       where rownum <= 1
         and sw.k > sw.d;
    
      -- 周线级别KD金叉，并且是成功的交易
      if v_exist > 0 and v_kd_gold_cross.profit_loss > 0 then
        v_success_num := v_success_num + 1;
      end if;
      -- 周线级别KD金叉的交易
      if v_exist > 0 then
        v_num := v_num + 1;
      end if;
    end loop;
    close cur_kd_gold_cross;
  
    -- 打印成功率
    pkg_tool.DEBUG('KD金叉，并且周线级别KD金叉的成功率是：' || v_success_num / v_num * 100);
  end CAL_SUCCESS_RATE_WITH_W_KD_G_C;*/

  

  /*--------------------- 海龟交易法则 ---------------------*/
  procedure turtle as
  
  begin
    null;
  end turtle;

  /*--------------------- 反趋势交易 ---------------------*/
  procedure countertrend as
  begin
    null;
  end countertrend;
end PKG_TEST;